package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DatabaseHelper;
import com.moleq.models.CustomerSetting;
import com.moleq.models.GlobalInfo;

public class CustomerSettingService {
	
	public static final int CustomerID = 0;
	public static final int FirstName = 1;
	public static final int LastName = 2;
	private static final int PageSize = 25;
	private DatabaseHelper DBHelper;
	private Context mContext;
	public CustomerSettingService() {
		
	}
	public CustomerSettingService(Context context){
		mContext = context;
		this.DBHelper = new DatabaseHelper(GlobalInfo.getInstance().ShareContext);
	}
	
	public List<CustomerSetting> getCustomerSetting(int searchType,int page,String SearchText){
		List<CustomerSetting> list = new ArrayList<CustomerSetting>();
		String sql = JoinPageSql(searchType,page,SearchText);
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
//		Cursor cursor = dbHelper.select(sql);
		if(cursor == null) return null;
		while(cursor.moveToNext()){
			CustomerSetting item = new CustomerSetting();
			item.setCs_no(cursor.getString(0));
			item.setCs_last_name(cursor.getString(1));
			item.setCs_first_name(cursor.getString(2));
			item.setCs_address_1(cursor.getString(3));
			item.setCs_address_2(cursor.getString(4));
			item.setCs_city(cursor.getString(5));
			item.setCs_state(cursor.getString(6));
			item.setCs_zip(cursor.getString(7));
			item.setCs_telephone(cursor.getString(8));
			item.setCs_email(cursor.getString(9));
			item.setCreate_datetime(cursor.getString(10));
			item.setUpdate_datetime(cursor.getString(11));
			list.add(item);
		}
		cursor.close();
		return list;
	}
	
	public int getCustomerMaxId(){
		String sql = "Select Max(cast(cs_no as bigint)) from t_customer_setting";
//		Cursor cursor = dbHelper.select(sql);
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if(cursor == null) return 0;
		if(cursor.moveToNext()){
			int max = cursor.getInt(0);
			cursor.close();
			return max;
		}else{
			cursor.close();
			return 0;
		}
	}
	
	public List<CustomerSetting> getCustomerSettingId(String no){
		String sql = "select * from t_customer_setting where cs_no = '" + no + "'";
//		Cursor cursor = dbHelper.select(sql);
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		List<CustomerSetting> list = new ArrayList<CustomerSetting>();
		if(cursor == null) return null;
		if(cursor.moveToNext()){
			CustomerSetting item = new CustomerSetting();
			item.setCs_no(cursor.getString(0));
			item.setCs_last_name(cursor.getString(1));
			item.setCs_first_name(cursor.getString(2));
			item.setCs_address_1(cursor.getString(3));
			item.setCs_address_2(cursor.getString(4));
			item.setCs_city(cursor.getString(5));
			item.setCs_state(cursor.getString(6));
			item.setCs_zip(cursor.getString(7));
			item.setCs_telephone(cursor.getString(8));
			item.setCs_email(cursor.getString(9));
			item.setCreate_datetime(cursor.getString(10));
			item.setUpdate_datetime(cursor.getString(11));
			cursor.close();
			list.add(item);
		}
		cursor.close();
		return list;
	}
	
	public String AddCustomerSetting(CustomerSetting obj){
		String sql = "insert into t_customer_setting VALUES (";
				sql += "'" + obj.getCs_no() + "',";
				sql += "'" + obj.getCs_last_name() + "',";
				sql += "'" + obj.getCs_first_name() + "',";
				sql += "'" + obj.getCs_address_1() + "',";
				sql += "'" + obj.getCs_address_2() + "',";
				sql += "'" + obj.getCs_city() + "',";
				sql += "'" + obj.getCs_state() + "',";
				sql += "'" + obj.getCs_zip() + "',";
				sql += "'" + obj.getCs_telephone() + "',";
				sql += "'" + obj.getCs_email() + "',";
				sql += "strftime('%Y%m%d%H%M%S','now'),";
				sql += "strftime('%Y%m%d%H%M%S','now'))";
//		String error = dbHelper.sqlExecute(sql);
		String error = DBHelper.execSQL(sql);
		return error;
	}
	
	public String updateCustomerSetting(CustomerSetting obj,String old_no){
		String sql = "update t_customer_setting set ";
				sql += "cs_no = '" + obj.getCs_no() + "',";
				sql += "cs_last_name = '" + obj.getCs_last_name() + "',";
				sql += "cs_first_name = '" + obj.getCs_first_name() + "',";
				sql += "cs_address_1 = '" + obj.getCs_address_1() + "',";
				sql += "cs_address_2 = '" + obj.getCs_address_2() + "',";
				sql += "cs_city = '" + obj.getCs_city() + "',";
				sql += "cs_state = '" + obj.getCs_state() + "',";
				sql += "cs_zip = '" + obj.getCs_zip() + "',";
				sql += "cs_telephone = '" + obj.getCs_telephone() + "',";
				sql += "cs_email = '" + obj.getCs_email() + "',";
				sql += "update_datetime = strftime('%Y%m%d%H%M%S','now') ";
				sql += "where cs_no = '" + old_no + "'";
//		String error = dbHelper.sqlExecute(sql);
		String error = DBHelper.execSQL(sql);
		return error;
	}
	
	public String JoinPageSql(int searchType,int page,String SearchText) {
		String sql = "select * from t_customer_setting";
		switch (searchType) {
		case CustomerID:
			sql += " where cs_no = '" + SearchText +"' order by cs_no Limit "
			+ String.valueOf(PageSize) + " Offset "
			+ String.valueOf(page * PageSize);
			break;
		case FirstName:
			sql += " where cs_first_name like '" + SearchText +"%' order by cs_no Limit "
					+ String.valueOf(PageSize) + " Offset "
					+ String.valueOf(page * PageSize);
			break;
		case LastName:
			sql += " where cs_last_name like '" + SearchText +"%' order by cs_no Limit "
					+ String.valueOf(PageSize) + " Offset "
					+ String.valueOf(page * PageSize);
			break;
		}		
		return sql;
	}
	
	
	public int getCountByCustomerNo(String num){
		return 0;
	}
	
	public int getCountByFirstName(String name){
		return 0;
	}
	
	public int getCountByLastName(String name){
		
		return 0;
	}
	
//	public int getCountByColumnName(String name){
//		int result = 0;
//		if(name = customerno){
//			result = getCountByCustomerNo(name);
//		}if()
//		
//		return result;
//	}
//	
//	public int getCountSize(int searchType,String SearchText){
//int result = 0;
//		switch (searchType) {
//		case SpinnerItems.CustomerID:
//			result = getCountByCustomerNo(SearchText);
//			break;
//		case SpinnerItems.FirstName:
//			result = getCountByFirstName(SearchText);
//			break;
//		case SpinnerItems.LastName:
//			result = getCountByLastName(SearchText);
//			break;
//			
//			return result;
//	}
	
	
	public int GetCountSize(int searchType,String SearchText){
		String sql = "select count(*) from t_customer_setting";
		switch (searchType) {
		case CustomerID:
			sql += " where cs_no = '" + SearchText +"' order by cs_no";
			break;
		case FirstName:
			sql += " where cs_first_name like '" + SearchText +"%' order by cs_no";
			break;
		case LastName:
			sql += " where cs_last_name like '" + SearchText +"%' order by cs_no";
			break;
		}
//		Cursor cursor = dbHelper.select(sql);
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if(cursor == null) return 0;
		int result = 0;
		if(cursor.moveToNext()){
			result = cursor.getInt(0);
			cursor.close();
			return result;
		}else{
			cursor.close();
			return result;
		}
	}
}
